package procedure;

/**
 * Collection of SQL Strings for operations related to Scenarios. These operate
 * on the following tables: Scenario, InputFile, AnalysisFiles, TrajectoryFiles
 * 
 * @author Daniel Funk
 * @author Yudi Xue
 */
public interface SQLScenarioStringsI {
	public static final String SCENARIO_INSERT = "INSERT INTO Scenario (s_identity, s_version, s_update_time, s_name, s_creator, s_type, s_creationDate, s_description, s_mv_identity, s_sc_identity, s_ps_ID) "
			+ "VALUES ('%s', '%s', CURRENT_TIMESTAMP, '%s', '%s', '%s', CURRENT_TIMESTAMP, '%s', '%s', '%s', %d)";

	public static final String SCENARIO_SELECT = "SELECT s_identity, s_version, s_update_time, s_name, s_creator, s_type, s_creationDate, s_description, s_mv_identity, s_sc_identity, s_ps_ID FROM Scenario WHERE s_identity = '%s'";

	public static final String SCENARIO_SELECT_IDENTITY = "SELECT s_identity FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_VERSION = "SELECT s_version FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_LAST_UPDATE_TIME = "SELECT s_update_time FROM Scenario WHERE s_identity = '%s'";

	public static final String SCENARIO_SELECT_NAME = "SELECT s_name FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_CREATOR = "SELECT s_creator FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_TYPE = "SELECT s_type FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_CREATIONDATE = "SELECT s_creationDate FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_RUNSTARTTIME = "SELECT s_runStartTime FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_RUNENDTIME = "SELECT s_runEndTime FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_RUNUSER = "SELECT s_runUser FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_DESCRIPTION = "SELECT s_description FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_MVERSIONID = "SELECT s_mv_identity FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_MVERSION_TYPE = "SELECT mv_type FROM MVersion WHERE mv_identity = (SELECT s_mv_identity FROM Scenario WHERE s_identity = '%s')";

	public static final String SCENARIO_SELECT_SCID = "SELECT s_sc_identity FROM Scenario WHERE s_identity = '%s'";
	public static final String SCENARIO_SELECT_PSID = "SELECT s_ps_ID FROM Scenario WHERE s_identity = '%s'";

	public static final String SCENARIO_SELECT_PARENT_MVERSION_BASELINE_SCENARIOID = "SELECT mv_baseline_s FROM MVersion WHERE mv_identity = (SELECT s_mv_identity FROM Scenario WHERE s_identity = '%s') ";

	public static final String SCENARIO_SELECT_INPUTFILEIDS = "SELECT if_f_identity FROM InputFiles WHERE if_s_identity = '%s'";
	public static final String SCENARIO_SELECT_INPUTFILENAMES = "SELECT f_name FROM File WHERE f_identity = (SELECT if_f_identity FROM InputFiles WHERE if_s_identity = '%s')";

	public static final String SCENARIO_SELECT_TRAJECTORYFILEIDS = "SELECT tf_f_identity FROM TrajectoryFiles WHERE tf_s_identity = '%s'";
	public static final String SCENARIO_SELECT_ANALYSISFILEIDS = "SELECT af_f_identity FROM AnalysisFiles WHERE af_s_identity = '%s'";

	public static final String SCENARIO_SET_PARENT_MVERSION_BASELINE_SCENARIOID = "UPDATE MVersion SET mv_baseline_s = '%s' WHERE mv_identity = (SELECT s_mv_identity FROM Scenario WHERE s_identity = '%s') ";
	public static final String SCENARIO_SET_VERSION = "UPDATE Scenario SET s_version = '%s' , s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";

	public static final String SCENARIO_SET_NAME = "UPDATE Scenario SET s_name = '%s', s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";
	public static final String SCENARIO_SET_CREATOR = "UPDATE Scenario SET s_creator = '%s', s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";
	public static final String SCENARIO_SET_TYPE = "UPDATE Scenario SET s_type = '%s', s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";
	public static final String SCENARIO_SET_DESCRIPTION = "UPDATE Scenario SET s_description = '%s', s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";
	public static final String SCENARIO_SET_SC = "UPDATE Scenario SET s_sc_identity = '%s', s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";

	public static final String SCENARIO_SET_RUNSTARTTIME = "UPDATE Scenario SET s_runStartTime = CURRENT_TIMESTAMP, s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";
	public static final String SCENARIO_SET_RUNENDTIME = "UPDATE Scenario SET s_runEndTime = CURRENT_TIMESTAMP, s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";
	public static final String SCENARIO_SET_RUNUSER = "UPDATE Scenario SET s_runUser = '%s', s_update_time = CURRENT_TIMESTAMP WHERE s_identity = '%s'";

	public static final String SCENARIO_DELETE = "DELETE FROM Scenario WHERE s_identity = '%s'";

	public static final String SCENARIO_SELECT_ASSOCIATEDFILEIDS = "SELECT s2f_f_identity FROM S2F WHERE s2f_s_identity = '%s'";

	// uuid, uuid, sid, sid, sid, sid, sid, sid, sid, sid, sid, sid
	public static final String SCENARIO_COPY = "INSERT INTO Scenario (s_identity, s_version, s_update_time, s_name, s_creator, s_type, s_creationDate, s_runStartTime, s_runEndTime, s_runUser, s_description, s_mv_identity, s_sc_identity, s_ps_ID) "
			+ "VALUES('%s', '%s', CURRENT_TIMESTAMP, "
			+ "(SELECT s_name FROM Scenario WHERE s_identity = '%s'), "
			+ "(SELECT s_creator FROM Scenario WHERE s_identity = '%s'), "
			+ "(SELECT s_type FROM Scenario WHERE s_identity = '%s'), "
			+ "CURRENT_TIMESTAMP, "
			+ "(SELECT s_runStartTime FROM Scenario WHERE s_identity = '%s'), "
			+ "(SELECT s_runEndTime FROM Scenario WHERE s_identity = '%s'), "
			+ "(SELECT s_runUser FROM Scenario WHERE s_identity = '%s'), "
			+ "(SELECT s_description FROM Scenario WHERE s_identity = '%s'), "
			+ "(SELECT s_mv_identity FROM Scenario WHERE s_identity = '%s'), "
			+ "'%s', %d)";

	// remote procedure call
	// @Deprecated this is only used for relational databases.
	public static final String SCENARIO_COPY_SP = "call duplicate_Scenario ('%s', '%s', ?)";
}
